package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import uteis.Constantes;
import model.Clientes;
import model.ClientesListar;

public class ClientesJDBCCadastro implements ClientesCadastroDAO {

	Connection conexao;

	public ClientesJDBCCadastro(Connection conexao) {

		this.conexao = conexao;
	}

	public String salvar(Clientes clientes) {
		String sqlquery = "insert into CLIENTES(cduf, cdstcliente, cdperiodo, nmcliente, rzsocial, tppessoa, dsendereco, nmbairro, nmcidade, nucxpostal, "
				+ "nucep, nufone1, nufone2, nufone3, nufax, nuinscest, nucnpj, nuidentid, dtidentid, nmorgemissor, "
				+ "nmnacionalidade, nmnaturalidade, nucpf, cdsexo, cdestcivil, dtnascimento, nmmae, nmpai, nmconjuge, email, "
				+ "homepage, vllimite, dtlimite, dtfundacao, dtcadastro, bobloqueado, bocomprador, usuinclusao, compinclusao, usualteracao, "
				+ "compalteracao, cdufnaturalidade, dtinclusao, dtalteracao, dsenderecoalt, nmbairroalt, nmcidadealt, nucepalt, "
				+ "nufonealt, cdufalt, percdescfechaperiodo, cdtabelapreco, nmempresa, dtadmissao, cdfuncao, vlsalario, cdrota, bolimportado, "
				+ "vlmaximodesctroca, percacrescimotroca, vlbonus, bolalterado, ipv4, codmunempresa, bolclienteatacado, cdempresa) "
				+ "values(?,?,?,?,?,?,?,?,?,?, "
				+ "?,?,?,?,?,?,?,?,?,?, "
				+ "?,?,?,?,?,?,?,?,?,?, "
				+ "?,?,?,?,?,?,?,?,?,?, "
				+ "?,?,?,?,?,?,?,?, "
				+ "?,?,?,?,?,?,?,?,?,?, "
				+ "?,?,?,?,?,?,?,?);";

		PreparedStatement pstmt;
		try {

			pstmt = this.conexao.prepareStatement(sqlquery);
			pstmt.setString(1, Constantes.ufselect);
			pstmt.setInt(2, 1);
			pstmt.setInt(3, 1);
			pstmt.setString(4, clientes.getNmclientes());
			pstmt.setString(5, clientes.getRzsocialclientes());
			pstmt.setString(6, clientes.getTppessoaclientes());
			pstmt.setString(7, clientes.getDsenderecoclientes());
			pstmt.setString(8, clientes.getNmbairroclientes());
			pstmt.setString(9, clientes.getNmcidadeclientes());
			pstmt.setInt(10, clientes.getNucxpostalclientes());
			pstmt.setString(11, clientes.getNucepclientes());
			pstmt.setString(12, clientes.getNufone1clientes());
			pstmt.setString(13, clientes.getNufone2clientes());
			pstmt.setString(14, clientes.getNufone3clientes());
			pstmt.setString(15, clientes.getNufaxclientes());
			pstmt.setString(16, clientes.getNuinscestclientes());
			pstmt.setString(17, clientes.getNucnpjclientes());
			pstmt.setString(18, clientes.getNuidentidclientes());
			pstmt.setDate(19, new java.sql.Date(clientes.getDtidentidclientes()
					.getTime()));
			pstmt.setString(20, clientes.getNmorgemissorclientes());
			pstmt.setString(21, clientes.getNmnacionalidadeclientes());
			pstmt.setString(22, clientes.getNmnaturalidadeclientes());
			pstmt.setString(23, clientes.getNucpfclientes());
			pstmt.setString(24, Constantes.sexocliente);
			pstmt.setString(25, Constantes.estcivilcliente);
			pstmt.setDate(26, new java.sql.Date(clientes
					.getDtnascimentoclientes().getTime()));
			pstmt.setString(27, clientes.getNmmaeclientes());
			pstmt.setString(28, clientes.getNmpaiclientes());
			pstmt.setString(29, clientes.getNmconjugeclientes());
			pstmt.setString(30, clientes.getEmailclientes());

			pstmt.setString(31, clientes.getHomepageclientes());
			pstmt.setInt(32, 0);
			pstmt.setDate(33, null);
			pstmt.setDate(34, null);
			pstmt.setDate(35, new java.sql.Date(clientes
					.getDtcadastroclientes().getTime()));
			pstmt.setString(36, clientes.getBobloqueadoclientes());
			pstmt.setString(37, clientes.getBocompradorclientes());
			pstmt.setString(38, clientes.getUsuinclusaoclientes());
			pstmt.setString(39, clientes.getCompinclusaoclientes());
			pstmt.setString(40, clientes.getUsualteracaoclientes());

			pstmt.setString(41, clientes.getCompalteracaoclientes());
			pstmt.setInt(42, 1);
			pstmt.setDate(43, new java.sql.Date(clientes
					.getDtinclusaoclientes().getTime()));
			// pstmt.setTime(44, new
			// java.sql.Time(clientes.getHrinclusaoclientes().getTime()));
			pstmt.setDate(44, new java.sql.Date(clientes
					.getDtalteracaoclientes().getTime()));
			// pstmt.setTime(46, new
			// java.sql.Time(clientes.getHralteracaoclientes().getTime()));
			pstmt.setString(45, clientes.getDsenderecoaltclientes());
			pstmt.setString(46, clientes.getNmbairroaltclientes());
			pstmt.setString(47, clientes.getNmcidadealtclientes());
			pstmt.setInt(48, 0);

			pstmt.setInt(49, 0);
			pstmt.setInt(50, 0);
			pstmt.setInt(51, 0);
			pstmt.setInt(52, 0);
			pstmt.setString(53, clientes.getNmempresaclientes());
			pstmt.setDate(54, null);
			pstmt.setInt(55, 0);
			pstmt.setInt(56, 0);
			pstmt.setInt(57, 0);
			pstmt.setString(58, "I");

			pstmt.setInt(59, 0);
			pstmt.setInt(60, 0);
			pstmt.setInt(61, 0);
			pstmt.setString(62, null);
			pstmt.setString(63, null);
			pstmt.setString(64, null);
			pstmt.setString(65, "N");
			pstmt.setString(66, clientes.getCdempresa());

			// pstmt.setDate(9, new java.sql.Date(
			// usuarios.getHrinclusaologinusuario().getTime() ));

			// System.out.println("comando sql:"+sqlquery);
			// pstmt.execute();
			String valor = "";

			if (!(clientes.getNucnpjclientes() == null || "".equals(clientes
					.getNucnpjclientes().trim()))) {
				pstmt.execute();
				sqlquery = "Select CDCLIENTE from clientes where nucnpj = '"
						+ clientes.getNucnpjclientes() + "';";
				java.sql.Statement stmt = conexao.createStatement();
				ResultSet rs = null;
				rs = stmt.executeQuery(sqlquery);
				while (rs.next()) {
					valor = rs.getString("CDCLIENTE");
				}
			} else if (!(clientes.getNucpfclientes() == null)
					|| "".equals(clientes.getNucpfclientes().trim())) {
				pstmt.execute();
				sqlquery = "Select CDCLIENTE from clientes where nucpf = '"
						+ clientes.getNucpfclientes() + "';";
				java.sql.Statement stmt = conexao.createStatement();
				ResultSet rs = null;
				rs = stmt.executeQuery(sqlquery);
				while (rs.next()) {
					valor = rs.getString("CDCLIENTE");
				}
			}
			return (valor);
		} catch (Exception e) {
			e.printStackTrace();
			return ("error");
		}
	}

	public boolean localizarCPFCNPJ(String cpfcnpj) {
		String sqlquery = "select CDCLIENTE from clientes where nucnpj = '"
				+ cpfcnpj + "' or nucpf = '" + cpfcnpj + "';";

		try {
			String valor = "";
			java.sql.Statement stmt = conexao.createStatement();
			ResultSet rs = null;
			rs = stmt.executeQuery(sqlquery);
			while (rs.next()) {
				valor = rs.getString("CDCLIENTE");
			}
			if (valor.equals("")) {
				return (false);
			} else {
				return (true);
			}

		} catch (Exception e) {
			e.printStackTrace();
			return (false);
		}
	}

	public String localizarSexoSelect(String selectsexo) {
		try {
			if (selectsexo.equals("Feminino")) {
				return ("1");
			} else {
				return ("2");
			}

		} catch (Exception e) {
			e.printStackTrace();
			return ("");
		}
	}

	public String localizarEstCivil(String estcivil) {
		String resp = "";
		try {
			if (estcivil.equals("Casado")) {
				resp = "1";
			} else if (estcivil.equals("Viuvo")) {
				resp = "2";
			} else if (estcivil.equals("Divorciado")) {
				resp = "3";
			} else if (estcivil.equals("Separado")) {
				resp = "4";
			} else if (estcivil.equals("Solteiro")) {
				resp = "5";
			}

		} catch (Exception e) {
			e.printStackTrace();
			return ("6");
		}
		return resp;
	}

	public String localizarUFSelect(String ufselect) {
		String sqlquery = "SELECT CDUF FROM UF WHERE (NMUF like '%" + ufselect
				+ "%') or (SGUF like '%" + ufselect + "%') ORDER BY NMUF ASC;";
		String valor = "";
		try {
			java.sql.Statement stmt = conexao.createStatement();
			ResultSet rs = null;
			rs = stmt.executeQuery(sqlquery);
			while (rs.next()) {
				valor = rs.getString("CDUF");
			}

		} catch (Exception e) {
			e.printStackTrace();
			return ("");
		}
		return valor;
	}

	public List<String> ListarUF(String nmuf) {

		List<String> result = new ArrayList<String>();
		if (!nmuf.trim().equals("")) {
			String sql = "SELECT NMUF FROM UF WHERE (NMUF like '%" + nmuf
					+ "%') or (SGUF like '%" + nmuf + "%') ORDER BY NMUF ASC;";
			String snmuf = "";
			try {
				java.sql.Statement stmt = conexao.createStatement();
				ResultSet valuetpservico = stmt.executeQuery(sql);

				valuetpservico.first();
				while (!valuetpservico.isAfterLast()) {
					snmuf = valuetpservico.getString("NMUF");
					result.add(snmuf);
					// result.add(scdtpservico+" | "+snmtpservico);
					valuetpservico.next();
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("VALOR EM BRANCO " + nmuf + " ------------- "
					+ result);
		}
		return result;
	}

	public List<String> ListarClienteNome(String nmcli) {

		List<String> result = new ArrayList<String>();
		if (!nmcli.trim().equals("")) {
			String sql = "SELECT NMCLIENTE FROM CLIENTES WHERE (NMCLIENTE like '%"
					+ nmcli + "%') ORDER BY NMCLIENTE ASC;";
			String snmcli = "";
			try {
				java.sql.Statement stmt = conexao.createStatement();
				ResultSet valuetpservico = stmt.executeQuery(sql);

				valuetpservico.first();
				while (!valuetpservico.isAfterLast()) {
					snmcli = valuetpservico.getString("NMCLIENTE");
					result.add(snmcli);
					// result.add(scdtpservico+" | "+snmtpservico);
					valuetpservico.next();
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("VALOR EM BRANCO " + nmcli + " ------------- "
					+ result);
		}
		return result;
	}

	public ResultSet ListarClientesT() {
		ResultSet valor = null;

		String sql = "select C.CDCLIENTE CDCLIENTE, C.NMCLIENTE NMCLIENTE, C.NUCPF NUCPF, C.NUIDENTID NUIDENTID, DATE_FORMAT(C.DTNASCIMENTO, '%d/%m/%Y') AS DTNASCIMENTO , "
				+ "C.NUFONE1 NUFONE1, C.NUFONE2 NUFONE2, C.DSENDERECO DSENDERECO, C.NUCXPOSTAL NUCXPOSTAL, C.NMBAIRRO NMBAIRRO, C.NUCEP NUCEP, "
				+ "U.NMUF NMUF, C.CDESTCIVIL CDESTCIVIL, C.CDSEXO CDSEXO, C.NMCIDADE NMCIDADE, C.EMAIL EMAIL, DATE_FORMAT(C.DTIDENTID, '%d/%m/%Y') AS DTIDENTID, C.CDUF CDUF, "
				+ "C.NUFONE3 NUFONE3 from CLIENTES C, UF U "
				+ "where C.CDUF = U.CDUF " + "order by C.NMCLIENTE;";
		try {
			java.sql.Statement stmt = conexao.createStatement();
			valor = stmt.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		/**
		 * }else { System.out.println("VALOR EM BRANCO "+nmformapagto+
		 * " ------------- "+result ); }
		 **/
		return valor;
	}

	public ResultSet editarConsulta(String idcliente) {

		String sql = "select C.CDCLIENTE CDCLIENTE, C.NMCLIENTE NMCLIENTE, C.NUCPF NUCPF, C.NUIDENTID NUIDENTID, DATE_FORMAT(C.DTNASCIMENTO, '%d/%m/%Y') AS DTNASCIMENTO , "
				+ "C.NUFONE1 NUFONE1, C.NUFONE2 NUFONE2, C.DSENDERECO DSENDERECO, C.NUCXPOSTAL NUCXPOSTAL, C.NMBAIRRO NMBAIRRO, C.NUCEP NUCEP, "
				+ "U.NMUF NMUF, C.CDESTCIVIL CDESTCIVIL, C.CDSEXO CDSEXO, C.NMCIDADE NMCIDADE, C.EMAIL EMAIL, DATE_FORMAT(C.DTIDENTID, '%d/%m/%Y') AS DTIDENTID, C.CDUF CDUF, "
				+ "C.NUFONE3 NUFONE3 from CLIENTES C, UF U "
				+ "where C.CDUF = U.CDUF and C.CDCLIENTE = "
				+ idcliente
				+ " order by C.NMCLIENTE;";

		ResultSet value = null;
		try {
			java.sql.Statement stmt = conexao.createStatement();
			value = stmt.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return value;
	}

	public String editar(ClientesListar clientes, String idcliente) {
		String sqlquery = "update clientes set NMCLIENTE = ?, DTNASCIMENTO = ?, NUFONE1 = ?, NUFONE2=?, DSENDERECO = ?, NUCXPOSTAL = ?, "
				+ "NMBAIRRO = ?, NUCEP = ?, CDUF= ?, CDSEXO = ?, CDESTCIVIL = ?, NMCIDADE = ?, EMAIL = ?, "
				+ "NUIDENTID = ?, DTIDENTID =?, NUFONE3=? where CDCLIENTE = "
				+ idcliente + ";";

		PreparedStatement pstmt;
		try {
			pstmt = this.conexao.prepareStatement(sqlquery);
			pstmt.setString(1, clientes.getNmcliente());
			// pstmt.setString(5, clientes.getNucpf());
			// pstmt.setString(6, clientes.getNuidentid());
			Date dtnasc = null;
			try {
				String date = clientes.getDtnascimento();
				SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
				dtnasc = dateFormat.parse(date);
			} catch (Exception e) {
				System.out.println("****Erro ao converter data*****");
			}
			pstmt.setDate(2, new java.sql.Date(dtnasc.getTime()));
			// pstmt.setString(2, clientes.getDtnascimento());
			pstmt.setString(3, clientes.getNufone1());
			pstmt.setString(4, clientes.getNufone2());
			pstmt.setString(5, clientes.getDsendereco());
			pstmt.setString(6, clientes.getNucxpostal());
			pstmt.setString(7, clientes.getNmbairro());
			pstmt.setString(8, clientes.getNucep());
			pstmt.setString(9, clientes.getCduf());
			pstmt.setString(10, clientes.getCdsexo());
			pstmt.setString(11, clientes.getCdestcivil());
			pstmt.setString(12, clientes.getNmcidade());
			pstmt.setString(13, clientes.getEmail());
			pstmt.setString(14, clientes.getNuidentid());
			Date dtindent = null;
			try {
				String date = clientes.getDtidentid();
				SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
				dtindent = dateFormat.parse(date);
			} catch (Exception e) {
				System.out.println("****Erro ao converter data*****");
			}
			pstmt.setDate(15, new java.sql.Date(dtindent.getTime()));
			// pstmt.setString(15, clientes.getDtidentid());
			pstmt.setString(16, clientes.getNufone3());

			pstmt.execute();

			return ("success");
		} catch (Exception e) {
			e.printStackTrace();
			return ("error");
		}
	}

	public void excluir(Clientes clientes) {
	}
}
